package com.app.mapper;

import com.app.model.Fw;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import io.swagger.models.auth.In;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 房屋 Mapper 接口
 * </p>
 *
 * @author yoominic
 * @since 2025-02-27
 */
public interface FwMapper extends BaseMapper<Fw> {

    List<Fw> getLoginUserFws(@Param("userName") String userName,@Param("idCard") String idCard,@Param("certificateNumber") String certificateNumber);

    IPage<Fw> adminPage(Page<Fw> fwPage,
                        @Param("houseType") String houseType,
                        @Param("houseStatus") String houseStatus,
                        @Param("houseKeyword") String houseKeyword,
                        @Param("lesseeKeyword") String lesseeKeyword);


    Fw getFwInfoById(@Param("id") Integer id);

    Fw getFwInfoByIdValue(@Param("idValue") String idValue);


    @Select("select distinct a.id,a.id_value,IFNULL(b.face_url, '/file/face_phone.jpg') AS faceUrl,\n" +
            "                        IFNULL(b.second_tenant_name, b.first_tenant_name) AS tenantName,\n" +
            "                        IFNULL(b.second_tenant_id_card, b.first_tenant_id_card) AS tenantIdCard,\n" +
            "                        IFNULL(b.second_tenant_phone, b.first_tenant_phone) AS tenantPhone,\n" +
            "                        a.house_type,a.area_name,a.street_name,a.live_status,\n" +
            "                        a.building_number,\n" +
            "                        a.unit_number,a.floor_level,a.room_number,\n" +
            "                        b.lease_certificate_number,a.house_direction,\n" +
            "                        a.structure_type,a.house_area_type,\n" +
            "                        a.building_area,a.usable_area,\n" +
            "                        lx.unit_price_per_square AS unitPricePerSquare,\n" +
            "                        lq.xs_value AS lqValue,lc.xs_value AS lcValue,\n" +
            "                        h.address_value as addressValue,\n" +
            "                        tr.new_deposit as newDeposit,\n" +
            "                        tr.new_deposit_returned as newDepositReturned,\n" +
            "                        ROUND(lx.unit_price_per_square * (1 + (COALESCE(lq.xs_value / 100, 0) + COALESCE(lc.xs_value / 100, 0))), 4) AS calculatedPrice,\n" +
            "                        CASE  WHEN a.house_type IN ('廉租房', '直管公房') THEN  ROUND(a.usable_area * ROUND(lx.unit_price_per_square * (1 + (COALESCE(lq.xs_value / 100, 0) + COALESCE(lc.xs_value / 100, 0))), 4), 2)\n" +
            "                              WHEN a.house_type = '公租房' THEN ROUND(a.building_area * ROUND(lx.unit_price_per_square * (1 + (COALESCE(lq.xs_value / 100, 0) + COALESCE(lc.xs_value / 100, 0))), 4), 2)  ELSE 0 END AS totalPrice,\n" +
            "                        (select max(jz.until_pay_time) from fwjz jz where jz.unique_id  = a.id_value) as expirationDate,a.remarks,\n" +
            "                        (select max(lease_relationship_time) from tb_tenant_relationship tr where tr.unique_id =  a.id_value) as latestLeaseRelationshipTime\n," +
            "                        (select max(payment_end)  from  tb_fw_renewal_record rr where rr.id_value = a.id_value) as fwPaymentEnd,\n" +
            "    CASE  WHEN NOT EXISTS (SELECT 1 FROM tb_fw_renewal_record rr WHERE rr.id_value = a.id_value AND CURDATE() BETWEEN rr.payment_start AND rr.payment_end) THEN 1\n" +
            "          WHEN EXISTS (SELECT 1  FROM tb_fw_renewal_record rr  WHERE rr.id_value = a.id_value  AND CURDATE() BETWEEN rr.payment_start AND rr.payment_end AND rr.status = 2  ) THEN 2\n" +
            "          WHEN EXISTS ( SELECT 1  FROM tb_fw_renewal_record rr  WHERE rr.id_value = a.id_value  AND CURDATE() BETWEEN rr.payment_start AND rr.payment_end AND rr.status = 1  ) THEN 3\n" +
            "          WHEN EXISTS ( SELECT 1  FROM tb_fw_renewal_record rr  WHERE rr.id_value = a.id_value  AND CURDATE() BETWEEN rr.payment_start AND rr.payment_end AND rr.status = 3  ) THEN 4 \n" +
            "             ELSE NULL END AS renewalRecordStatus \n"+
            "        from fw a\n" +
            "                 left join yonghu b on a.id_value = b.unique_id\n" +
            "                 left join fwlx lx on lx.house_type_name = a.house_type\n" +
            "                 left join fw_lcwh lc on lc.lc_value = a.floor_level\n" +
            "                 left join fwlq lq on lq.lq_value = a.house_area_type\n" +
            "                 left join fwbh h on h.unique_id = a.id_value\n" +
            "                 left join tb_user_fw_join ufj on ufj.fw_id = a.id\n" +
            "                 left join tb_tenant_relationship tr on tr.unique_id = a.id_value\n" +
            "        where 1=1 and ufj.user_id = #{userId}")
    List<Fw> getLoginUserJoinFws(@Param("userId") Integer userId);


    @Select("select f.id,f.area_name,f.street_name,\n" +
            "case when f.building_number is not null then f.building_number else f.courtyard_number end buildingNumber,\n" +
            "f.unit_number,f.floor_level,f.room_number,f.mph_value,\n" +
            "tr.face_url,tr.tenant_name,concat(left(tr.tenant_id_card, 3),'********',right(tr.tenant_id_card, 4)) as tenant_id_card,tr.tenant_phone,\n" +
            "tr.family_members_json as familyMembersJsonStr\n" +
            "from tb_user_fw_join ufj\n" +
            "left join fw f on f.id = ufj.fw_id\n" +
            "left join tb_tenant_relationship tr on tr.unique_id = f.id_value and tr.tenant_id_card = f.current_tenant_id_card\n" +
            "where 1=1 and ufj.user_id = #{userId}")
    List<Fw> getLoginUserFwJoinYonghuInfo(@Param("userId") Integer userId);



    IPage<Fw> adminGetFwInfoList(Page<Fw> fwPage, @Param("houseType") String houseType,
                                 @Param("houseStatus") String houseStatus,@Param("houseKeyword") String houseKeyword,
                                 @Param("lesseeKeyword") String lesseeKeyword,@Param("uniqueId") String uniqueId);


    Fw adminGetFwInfoById(@Param("id") Integer id);
}
